<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML" 4.01"//EN" "http://www.w3.org/TR/html4/strict.dtd">
<!-- Created: August 17, 2001-->
<!--
$Id: RepTemplate.php,v 1.2 2001/11/02 15:00:10 eugenem Exp eugenem $
$Header: /export/home/eugenem/public_html/ComplianceReports/RCS/RepTemplate.php,v 1.2 2001/11/02 15:00:10 eugenem Exp eugenem $
$Log: RepTemplate.php,v $
Revision 1.2  2001/11/02 15:00:10  eugenem
Moved database connect statements to a separate file.

This report returns a list of all machines with a survey due date within the next 45 days.
-->

<html>
<head>
<title>Radiological Equipment Database</title>
<meta name="author" content="Eugene Mah">
<link rev="made" href="mailto:eugenem@ix.netcom.com">
<link rev="home" href="http://home.netcom.com/~eugenem/">
<meta name="description" content="">
<meta name="Keywords" content="">
<meta name="Reply-to" content="eugenem@ix.netcom.com">
<META http-equiv="PICS-Label" content='(PICS-1.1 "http://www.classify.org/safesurf/" l r (SS~~0001))'>
<?PHP
require('functions.inc');
require('dbc.inc');

$today = date("Ymd");
$year = date("Y");
$month = date("m");
$day = date("d");
?>
</head>

<body>
<h1>Radiological Equipment Database</h1>
<h2>Surveys due within 45 days as of <?php echo $today; ?></h2>
<?PHP
/*
  Create temporary tables to hold the surveys from the previous year
  and this year
*/
$query = "CREATE TEMPORARY TABLE lastyear (
          MachineID int not null,
          TestDate date, 
          pSurveyID int default NULL,
          DueDate date,
          PRIMARY KEY MachineID ( MachineID ), 
          INDEX (pSurveyID), INDEX (TestDate), INDEX (DueDate))";
$result = mysql_query($query)
  or die ("Unable to perform query: $query");

// Select all the surveys from the previous year and insert them into the lastyear table
$query = $insStr . " INTO lastyear (MachineID,TestDate,pSurveyID,DueDate) 
          SELECT Survey2Machine.MachineID,TestDate,Survey2Machine.SurveyID,DATE_ADD(TestDate, INTERVAL 1 YEAR)
          FROM Survey2Machine
          LEFT JOIN TestDates USING (SurveyID)
          WHERE TestDate BETWEEN \"" . date("Ymd",mktime(0,0,0,1,1,$year-1)) . "\" AND \"" . date("Ymd",mktime(0,0,0,12,31,$year-1)) . "\"
          AND (TestDates.TypeID=1 OR TestDates.TypeID=2)";
$result = mysql_query($query)
  or die ("Unable to perform query: $query<br />\n" . mysql_error() . "<br />\n");

// Select surveys from the temporary table where the due date is between today and today + 45 days
// TODO: The list should check to see if the survey for the unit has already been done. If it has, don't print it
$query = "SELECT MachineID, Description, TestDate, DueDate from lastyear
          LEFT JOIN Machines USING (MachineID)
          WHERE DueDate between CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 45 DAY)
          AND Status=\"Active\"
          ORDER BY TestDate, Machines.MachineID";
$result = mysql_query($query)
  or die ("Unable to perform query: $query<br />\n" . mysql_error() . "<br />\n");

echo "<table border=\"0\" summary=\"Surveys due within 45 days\">\n";
echo "  <tr>\n";
echo "    <th>Machine</th><th>Last Survey</th><th>Due Date</th>\n";
echo "  </tr>\n";
while ($row = mysql_fetch_array($result)) {
  echo "  <tr>\n";
  echo "    <td><a href='SurveyAdd.php?MachID=" . $row['MachineID'] . "'>" . $row['Description'] . "</a></td>\n";
  echo "    <td>" . $row['TestDate'] . "</td>\n";
  echo "    <td>" . $row['DueDate'] . "</td>\n";
  echo "  </tr>\n";
}
echo "</table>\n";
?>
<p><a href="index.html" target="_top" >Main</a></p>
</body>
</html>
